import { Sequelize } from "sequelize";

interface Config {
  name: string;
  user: string;
  password: string;
  host: string;
  port: number;
}

// 直接从环境变量获取配置
const config: Config = {
  name: process.env.DB_NAME || "nuxt_db",
  user: process.env.DB_USER || "root",
  password: process.env.DB_PASSWORD || "",
  host: process.env.DB_HOST || "localhost",
  port: Number(process.env.DB_PORT) || 3306,
};

// 初始化Sequelize实例
const sequelize = new Sequelize(config.name, config.user, config.password, {
  host: config.host,
  port: Number(config.port),
  dialect: "mysql",
  logging: false,
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000,
  },
});

// 检查并修复posts表结构
const checkAndFixPostsTable = async () => {
  try {
    // 检查posts表是否存在
    const [tables] = await sequelize.query("SHOW TABLES LIKE 'posts'");
    if (tables.length === 0) {
      console.log("posts表不存在，将自动创建");
      return;
    }

    // 检查表结构，如果缺少必要字段则删除表重新创建
    const [columns] = await sequelize.query("SHOW COLUMNS FROM posts");
    const columnNames = columns.map((col: unknown) => (col as { Field: string }).Field);

    const requiredFields = [
      "viewCount",
      "likeCount",
      "replyCount",
      "isPinned",
      "isLocked",
    ];
    const missingFields = requiredFields.filter(
      (field) => !columnNames.includes(field)
    );

    if (missingFields.length > 0) {
      console.log(`发现缺失字段: ${missingFields.join(", ")}`);
      console.log("删除现有posts表，将重新创建...");

      // 删除现有表
      await sequelize.query("DROP TABLE IF EXISTS posts");
      console.log("已删除现有posts表");
    } else {
      console.log("posts表结构完整，无需修改");
    }
  } catch (error) {
    console.error("检查posts表失败:", error);
  }
};

// 测试连接并同步表结构
const testConnectionAndSync = async () => {
  try {
    await sequelize.authenticate();
    console.log("数据库连接成功");

    // 导入所有模型
    const _User = (await import("./models/User")).default;
    const _Topic = (await import("./models/Topic")).default;
    const _Resume = (await import("./models/Resume")).default;
    const _Post = (await import("./models/Post")).default;
    const _Comment = (await import("./models/Comment")).default;
    const _Like = (await import("./models/Like")).default;

    // 建立模型关联
    _User.hasMany(_Topic, { foreignKey: "submitterId", as: "topics" });
    _Topic.belongsTo(_User, { foreignKey: "submitterId", as: "submitter" });
    _User.hasMany(_Resume, { foreignKey: "userId", as: "resumes" });
    _Resume.belongsTo(_User, { foreignKey: "userId", as: "user" });

    // Post模型关联
    _User.hasMany(_Post, { foreignKey: "authorId", as: "posts" });
    _Post.belongsTo(_User, { foreignKey: "authorId", as: "author" });
    
    // Comment模型关联
    _User.hasMany(_Comment, { foreignKey: "authorId", as: "comments" });
    _Comment.belongsTo(_User, { foreignKey: "authorId", as: "author" });
    _Post.hasMany(_Comment, { foreignKey: "postId", as: "comments" });
    _Comment.belongsTo(_Post, { foreignKey: "postId", as: "post" });
    
    // 评论回复关联
    _Comment.hasMany(_Comment, { foreignKey: "parentId", as: "replies" });
    _Comment.belongsTo(_Comment, { foreignKey: "parentId", as: "parent" });
    
    // Like模型关联
    _User.hasMany(_Like, { foreignKey: "userId", as: "likes" });
    _Like.belongsTo(_User, { foreignKey: "userId", as: "user" });

    // 检查并修复posts表结构
    await checkAndFixPostsTable();

    // 只同步表结构，不强制更新（避免索引冲突）
    await sequelize.sync({ force: false, alter: false });
    console.log("数据库表结构同步完成");
  } catch (error) {
    console.error("Sequelize连接或同步失败:", error);
    // 如果同步失败，尝试只创建不存在的表
    try {
      await checkAndFixPostsTable();
      await sequelize.sync({ force: false, alter: false });
      console.log("数据库表结构同步完成（重试）");
    } catch (retryError) {
      console.error("重试同步仍然失败:", retryError);
    }
  }
};

testConnectionAndSync();

export default sequelize;
